<?php

namespace App\Http\Controllers\Database;


use Illuminate\Support\Facades\DB;


class CheckDbController extends BaseController
{


    /**
     * // 检查数据库
     * 1、检查数据库的所有表的引擎
     * 2、检查数据库所有表的字段 是否为空 与 默认值
     * 2、检查数据库所有表的utf8字段 转换成 utf8mb4
     */


    // 检查数据库的所有表的引擎
    // http://www.shop.com/database/check-db/engine
    public function engine()
    {
        $tables = $this->getTables();

        // 页面展示
        $style = "<style>table, th, td {border: 1px solid black;}</style>";
        $style = "<style>table, th, td {border: 1px solid #0a6aa1;}</style>";
        $html  = $style.'<table>';
        $html  .= '<tr><td>数据表</td><td>数据引擎</td><td>表名称</td><tr>';
        foreach($tables as $_table){
            $color = $_table['engine'] == 'InnoDB' ? '' : 'red';
            $html  .= '<tr style="background-color: '.$color.'"><td>'.$_table['tablename'].'</td><td>'.$_table['engine'].'</td><td>'.$_table['table_comment'].'</td><tr>';
        }
        echo $html.'</table>';
    }

    // 检查数据库所有表的字段 是否为空 与 默认值
    // http://www.shop.com/database/check-db/fields
    public function fields()
    {
        $db_name = env('DB_DATABASE');
        $where   = 'COLUMN_NAME=\'verify\'';  // 查询指定字段
        $where   = ' COLUMN_COMMENT=\'\' OR  IS_NULLABLE!=\'NO\' ';  // 查询为空的字段
        $fields  = $this->getFields($db_name, '', $where);

        // 页面展示
        $style = "<style>table, th, td {border: 1px solid black;}</style>";
        $style = "<style>table, th, td {border: 1px solid #0a6aa1;}</style>";
        $html  = $style.'<table>';
        $html  .= '<tr><td>数据表</td><td>字段</td><td>类型</td><td>是否为空</td><td>长度</td><td>数字长度</td><td>字节数</td><td>字段名</td><td>默认值</td><tr>';
        foreach($fields as $_field){
            if($_field['field'] == 'id'){
                continue;
            }
            $color = (($_field['is_null'] == 'YES') || is_null($_field['default'])) ? 'red' : '';
            $html  .= '<tr style="background-color: '.$color.'"><td>'.$_field['tablename'].'</td><td>'.$_field['field'].'</td><td>'.$_field['type'].'</td><td>'.$_field['is_null'].'</td><td>'.$_field['length'].'</td><td>'.$_field['num_length'].'</td><td>'.$_field['num_bit'].'</td><td>'.$_field['comment'].'</td><td>'.$_field['default'].'</td><tr>';
        }

        echo $html.'</table>';
    }

    // ALTER TABLE `tp6_sns_question_supply` MODIFY COLUMN `channel`  varchar(20) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '站点' AFTER `id`;
    // 将所有的数据表 改为 utf8mb4
    // http://www.shop.com/database/check-db/utf8mb4
    public function utf8mb4()
    {
        set_time_limit(0);
        $db_name = env('DB_DATABASE');
        $do_sql  = "SELECT `TABLE_NAME` tablename, `ENGINE` engine,TABLE_COMMENT table_comment FROM information_schema.TABLES WHERE TABLE_SCHEMA='".$db_name."'";
        $tables  = Db::select($do_sql);
        // 将对象转换成数组
        $tables = $this->arrayObjectToArray($tables);

        // 执行
        $html  = '';
        $cline = $this->cline();
        foreach($tables as $_table){
            $html .= '将数据表：'.$_table['tablename'].' 转换成：utf8mb4<br/>';
            $sql1 = 'ALTER TABLE `'.$_table['tablename'].'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;';
            $sql2 = 'ALTER TABLE `'.$_table['tablename'].'` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;';
            echo $sql1;
            echo $cline;
            echo $sql2;
            echo $cline;
            //Db::execute($sql1);
            //Db::execute($sql2);
        }

        echo $html;
    }


}


